# -*- coding: utf-8 -*-
import os
import sqlite3
import threading


class Student:
    def __init__(self, exam_id, name, class_name, total_score, total_school_rank, total_class_rank,
                 chinese_score, chinese_school_rank, chinese_class_rank,
                 math_score, math_school_rank, math_class_rank,
                 english_score, english_school_rank, english_class_rank,
                 physics_score, physics_school_rank, physics_class_rank,
                 chemistry_score, chemistry_school_rank, chemistry_class_rank,
                 biology_score, biology_school_rank, biology_class_rank):
        self.exam_id = exam_id
        self.name = name
        self.class_name = class_name
        self.total_score = total_score
        self.total_school_rank = total_school_rank
        self.total_class_rank = total_class_rank
        self.chinese_score = chinese_score
        self.chinese_school_rank = chinese_school_rank
        self.chinese_class_rank = chinese_class_rank
        self.math_score = math_score
        self.math_school_rank = math_school_rank
        self.math_class_rank = math_class_rank
        self.english_score = english_score
        self.english_school_rank = english_school_rank
        self.english_class_rank = english_class_rank
        self.physics_score = physics_score
        self.physics_school_rank = physics_school_rank
        self.physics_class_rank = physics_class_rank
        self.chemistry_score = chemistry_score
        self.chemistry_school_rank = chemistry_school_rank
        self.chemistry_class_rank = chemistry_class_rank
        self.biology_score = biology_score
        self.biology_school_rank = biology_school_rank
        self.biology_class_rank = biology_class_rank

class Database:
    def __init__(self, db_path):
        self.db_path = db_path
        self.conn = sqlite3.connect(db_path, check_same_thread=False)
        self.cursor = self.conn.cursor()
        self.lock = threading.Lock() # 创建一个线程锁，用于保护数据库操作
        self.create_table()

    def create_table(self):
        # 加锁
        with self.lock:
            # 创建成绩表
            self.cursor.execute('''
                        CREATE TABLE IF NOT EXISTS students (
                            id INTEGER PRIMARY KEY AUTOINCREMENT,
                            exam_id TEXT NOT NULL,
                            name TEXT NOT NULL,
                            class_name TEXT NOT NULL,
                            total_score REAL NOT NULL,
                            total_school_rank INTEGER NOT NULL,
                            total_class_rank INTEGER NOT NULL,
                            chinese_score REAL NOT NULL,
                            chinese_school_rank INTEGER NOT NULL,
                            chinese_class_rank INTEGER NOT NULL,
                            math_score REAL NOT NULL,
                            math_school_rank INTEGER NOT NULL,
                            math_class_rank INTEGER NOT NULL,
                            english_score REAL NOT NULL,
                            english_school_rank INTEGER NOT NULL,
                            english_class_rank INTEGER NOT NULL,
                            physics_score REAL NOT NULL,
                            physics_school_rank INTEGER NOT NULL,
                            physics_class_rank INTEGER NOT NULL,
                            chemistry_score REAL NOT NULL,
                            chemistry_school_rank INTEGER NOT NULL,
                            chemistry_class_rank INTEGER NOT NULL,
                            biology_score REAL NOT NULL,
                            biology_school_rank INTEGER NOT NULL,
                            biology_class_rank INTEGER NOT NULL
                        )
                    ''')
            # 创建分数线表
            self.cursor.execute('''
                       CREATE TABLE IF NOT EXISTS score_thresholds (
                           exam_id TEXT PRIMARY KEY,
                           threshold INTEGER NOT NULL
                       )
                   ''')
            self.conn.commit()

    def insert_student(self, student):
        # 加锁
        with self.lock:
            self.cursor.execute('''
                        INSERT INTO students (
                            exam_id, name, class_name, total_score, total_school_rank, total_class_rank,
                            chinese_score, chinese_school_rank, chinese_class_rank,
                            math_score, math_school_rank, math_class_rank,
                            english_score, english_school_rank, english_class_rank,
                            physics_score, physics_school_rank, physics_class_rank,
                            chemistry_score, chemistry_school_rank, chemistry_class_rank,
                            biology_score, biology_school_rank, biology_class_rank
                        )
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    ''', (
                student.exam_id,
                student.name, student.class_name, student.total_score, student.total_school_rank,
                student.total_class_rank,
                student.chinese_score, student.chinese_school_rank, student.chinese_class_rank,
                student.math_score, student.math_school_rank, student.math_class_rank,
                student.english_score, student.english_school_rank, student.english_class_rank,
                student.physics_score, student.physics_school_rank, student.physics_class_rank,
                student.chemistry_score, student.chemistry_school_rank, student.chemistry_class_rank,
                student.biology_score, student.biology_school_rank, student.biology_class_rank
            ))
            self.conn.commit()

    def get_students_by_class(self, class_name, exam_id=None):
        if exam_id:
            self.cursor.execute('SELECT * FROM students WHERE class_name = ? AND exam_id = ?', (class_name, exam_id))
        else:
            self.cursor.execute('SELECT * FROM students WHERE class_name = ?', (class_name,))
        return self.cursor.fetchall()

    def get_student_by_name(self, name):
        # 加锁
        with self.lock:
            self.cursor.execute('SELECT * FROM students WHERE name = ?', (name,))
            return self.cursor.fetchall()

    def get_class_statistics(self, exam_id):
        with self.lock:
            try:
                # 获取对应 exam_id 的分数线
                score_threshold = self.get_score_threshold(exam_id)
                if score_threshold is None:
                    raise ValueError(f"考试 {exam_id} 的分数线未设置！")
                # 查询班级成绩统计信息
                self.cursor.execute('''
                        SELECT class_name,
                               AVG(total_score) AS avg_score,
                               MAX(total_score) AS max_score,
                               MIN(total_score) AS min_score,
                               COUNT(CASE WHEN total_score > ? THEN 1 END) AS above_threshold_count
                        FROM students
                        WHERE exam_id = ?
                        GROUP BY class_name
                    ''', (score_threshold, exam_id))
                result = self.cursor.fetchall()
                # print(f"查询结果: {result}")  # 打印查询结果
                return result
            except Exception as e:
                print(f"数据库查询失败: {str(e)}")  # 打印异常信息
                raise e

    def reset_database(self):
        # 关闭当前数据库连接
        self.conn.close()
        # 删除数据库文件
        if os.path.exists(self.db_path):
            os.remove(self.db_path)
        # 重新创建数据库文件并初始化表结构
        self.conn = sqlite3.connect(self.db_path, check_same_thread=False)
        self.cursor = self.conn.cursor()
        self.create_table()

    def is_exam_imported(self, exam_id):
        self.cursor.execute('SELECT COUNT(*) FROM students WHERE exam_id = ?', (exam_id,))
        return self.cursor.fetchone()[0] > 0

    def get_all_exam_ids(self):
        self.cursor.execute('SELECT DISTINCT exam_id FROM students')
        return [row[0] for row in self.cursor.fetchall()]

    def set_score_threshold(self, exam_id, threshold):
        with self.lock:
            self.cursor.execute('''
                INSERT OR REPLACE INTO score_thresholds (exam_id, threshold)
                VALUES (?, ?)
            ''', (exam_id, threshold))
            self.conn.commit()

    def get_score_threshold(self, exam_id):
        # with self.lock:
        self.cursor.execute('SELECT threshold FROM score_thresholds WHERE exam_id = ?', (exam_id,))
        result = self.cursor.fetchone()
        return result[0] if result else None # 检查 result 是否为 None

    def set_score_thresholds(self, exam_thresholds):
        with self.lock:
            # 为每个 exam_id 设置对应的分数线
            for exam_id, threshold in exam_thresholds.items():
                self.cursor.execute('''
                    INSERT OR REPLACE INTO score_thresholds (exam_id, threshold)
                    VALUES (?, ?)
                ''', (exam_id, threshold))
            self.conn.commit()

    def get_class_score_distribution(self, exam_id, subject, class_name):
        subject_column = f"{subject}_score"
        self.cursor.execute(f'''
            SELECT FLOOR({subject_column} / 10) * 10 AS score_range,
                   COUNT(*) AS count
            FROM students
            WHERE exam_id = ? AND class_name = ?
            GROUP BY score_range
            ORDER BY score_range
        ''', (exam_id, class_name))
        return self.cursor.fetchall()

    def get_subject_statistics(self, exam_id, subject):
        """
        查询科目统计信息，包括平均分、最高分、最低分和及格人数。

        Args:
            exam_id (str): 考试标识符。
            subject (str): 科目。

        Returns:
            list: 包含班级统计信息的列表，每个元素为 (班级名称, 平均分, 最高分, 最低分, 及格人数)。
        """
        # 根据科目动态选择列名
        subject_column = f"{subject}_score"
        # 确定及格线
        if subject in ["chinese", "math", "english"]:
            pass_score = 90
        else:
            pass_score = 60

        # 构建 SQL 查询语句
        query = f'''
            SELECT class_name,
                   AVG({subject_column}) AS avg_score,
                   MAX({subject_column}) AS max_score,
                   MIN({subject_column}) AS min_score,
                   SUM(CASE WHEN {subject_column} >= ? THEN 1 ELSE 0 END) AS pass_count
            FROM students
            WHERE exam_id = ?
            GROUP BY class_name
        '''
        # 执行查询
        self.cursor.execute(query, (pass_score, exam_id))
        return self.cursor.fetchall()

    def get_subject_score_distribution(self, exam_id, subject):
        """
        获取指定考试中某科目的分数段分布。

        Args:
            exam_id (int): 考试的ID。
            subject_column (str): 科目的列名（如 'math_score'）。

        Returns:
            list: 包含分数段及其对应人数的列表，每个元素为一个元组（score_range, count）。
        """
        subject_column = f"{subject}_score"
        self.cursor.execute(f'''
            SELECT FLOOR({subject_column} / 10) * 10 AS score_range, COUNT(*) AS count
            FROM students
            WHERE exam_id = ?
            GROUP BY score_range
            ORDER BY score_range
        ''', (exam_id,))
        result = self.cursor.fetchall()
        if not result:  # 检查结果是否为空
            raise ValueError(f"没有找到考试 {exam_id} 的科目 {subject} 的数据！")
        return result

    def get_all_classes(self):
        """
            从 students 表中获取所有班级的名称。

            Returns:
                list: 所有班级的名称列表。
            """
        self.cursor.execute("SELECT DISTINCT class_name FROM students")
        classes = [row[0] for row in self.cursor.fetchall()]
        return classes


    def get_all_subjects(self):
        """
        从 students 表中获取所有科目。

        Returns:
            list: 所有科目的列表。
        """
        # 查询所有科目列名
        self.cursor.execute("PRAGMA table_info(students)")
        columns = self.cursor.fetchall()

        # 提取科目列名（以 '_score' 结尾的列）
        subjects = [col[1].replace('_score', '') for col in columns if col[1].endswith('_score')]
        return subjects

    def get_students_by_conditions(self, exam_id=None, subject=None, class_name=None, student_name=None):
        """
    根据动态条件查询学生成绩。

    Args:
        exam_id (str): 考试标识符。
        subject (str): 科目。
        class_name (str): 班级。
        student_name (str): 学生姓名。

    Returns:
        list: 符合条件的学生成绩列表。
    """
        # 动态构建查询语句
        if subject:
            # 如果指定了科目，只查询该科目的分数、学校排名和班级排名
            subject_score = f"{subject}_score"
            subject_school_rank = f"{subject}_school_rank"
            subject_class_rank = f"{subject}_class_rank"
            query = f"SELECT exam_id, name, class_name, {subject_score}, {subject_school_rank}, {subject_class_rank} FROM students WHERE 1=1"
        else:
            # 如果未指定科目，查询除了ID列的所有列
            query =  """
            SELECT exam_id, name, class_name, total_score, total_school_rank, total_class_rank,
                   chinese_score, chinese_school_rank, chinese_class_rank,
                   math_score, math_school_rank, math_class_rank,
                   english_score, english_school_rank, english_class_rank,
                   physics_score, physics_school_rank, physics_class_rank,
                   chemistry_score, chemistry_school_rank, chemistry_class_rank,
                   biology_score, biology_school_rank, biology_class_rank
            FROM students WHERE 1=1
        """

        params = []

        if exam_id:
            query += " AND exam_id = ?"
            params.append(exam_id)

        if class_name:
            query += " AND class_name = ?"
            params.append(class_name)

        if student_name:
            query += " AND name = ?"
            params.append(student_name)

        # 执行查询
        # print(query,params)
        self.cursor.execute(query, params)
        return self.cursor.fetchall()